from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Date
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from datetime import datetime
import os
import csv
import pandas as pd

base = declarative_base()


class WeatherRaw(base):
    __tablename__ = 'weather_raw'
    city = Column(String(80), nullable=False, primary_key=True)
    date = Column(Date, primary_key=True)
    temp_hi = Column(Integer)
    temp_lo = Column(Integer)
    short_description = Column(String(64))
    wind_direction = Column(String(10))
    wind_power = Column(String(10))


class DbUtil:
    def __init__(self):
        self.engine = create_engine('postgresql+psycopg2://postgres:sdf#s#234%#2!ddEwsewWEqq@apier/weather')
        self.db = scoped_session(sessionmaker(bind=self.engine))

    def create_db(self):
        base.metadata.create_all(self.engine)

    def add_data(self):
        data = WeatherRaw(city='shanghai', date=datetime.now(), temp_hi=23, temp_lo=19, short_description='晴天',
                          wind_direction='南风', wind_power='3级')
        self.db.add(data)
        self.db.commit()


class WeatherImporter:
    @staticmethod
    def import_weather(source_dir):
        if not os.path.exists(source_dir):
            print("path %s not exist" % source_dir)
            return



        for curr_file in os.listdir(source_dir):
            print('current file:',curr_file)
            if os.path.isfile(source_dir+'/'+curr_file) and os.path.splitext(curr_file)[1] == '.csv':
                print('parse csv file ', curr_file)
                csv_file=  pd.read_csv(source_dir + '/' + curr_file)



                csv_file = csv.reader(open(source_dir+'/'+curr_file, 'r'))
                for row in csv_file:
                    print(row)


if __name__ == '__main__':
    WeatherImporter.import_weather('/Users/yanjunhua/PycharmProjects/weather/data')
